package sy.service.impl;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Service;
import sy.pageModel.BaseEntity;
import sy.pageModel.DataGrid;
import sy.service.CodeServiceI;
import sy.util.StringUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2016-07-07.
 */
@Service
public class CodeServiceImpl implements CodeServiceI {

    @Autowired
    private JdbcTemplate template;

    @Override
    public List<BaseEntity> findList(String types) {
        String sql="select distinct types from s_code  where 1=1";
        if (!"".equals(types)) sql += "and types='"+types+"'";
        final List<BaseEntity> list=new ArrayList<>();
        template.query(sql, new Object[]{}, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                BaseEntity entity=new BaseEntity();
                entity.setId(rs.getString(1));
                entity.setText(rs.getString(1));
                list.add(entity);
            }
        });
        return list;
    }
    @Override
    public DataGrid detagrid(BaseEntity entity) {
        DataGrid dg = new DataGrid();
        String sql = "select count(*) from s_code  where 1=1  ";
        sql = addWhere(entity, sql);//模糊查询
        long total = template.queryForLong(sql);
        dg.setTotal(total);//获取总数
        sql = "select * from s_code  where 1=1  ";
        sql = addWhere(entity, sql);
        sql = addOrderBy(entity, sql);
        sql = addLimit(entity, sql);
        final List<BaseEntity> baseList = new ArrayList<BaseEntity>();
        template.query(sql, new RowCallbackHandler(){
            public void processRow(ResultSet rs) throws SQLException {
                BaseEntity base=new BaseEntity();
                base.setId(rs.getString("id"));
                base.setName(rs.getString("types"));
                base.setDeptId(rs.getString("code"));
                base.setText(rs.getString("descs"));
                base.setTypeName(rs.getString("typeid"));
                baseList.add(base);
            }});
        dg.setRows(baseList);
        return dg;
    }

    @Override
    public int addCodeService(BaseEntity entity) {
        String sql="INSERT INTO s_code(types, code, descs, typeid) VALUES (?,?,?,?)";
        int rows=template.update(sql,new Object[]{entity.getName(),entity.getDeptId(),entity.getText(),entity.getTypeName()});
        return rows;
    }

    @Override
    public int editCodeService(BaseEntity entity) {
        String sql="UPDATE s_code SET types=?, code=?, descs=?,typeid=? WHERE id=?";
        int rows=template.update(sql,new Object[]{entity.getName(),entity.getDeptId(),entity.getText(),entity.getTypeName(),entity.getId()});
        return rows;
    }

    @Override
    public int deleteCodeService(BaseEntity entity) {
        String sql="DELETE FROM s_code where id=?";
        int rows=0;
        String ids1[]=entity.getIds().split(",");
        for (int i = 0; i <ids1.length ; i++) {
            rows=template.update(sql,new Object[]{ids1[i]});
        }
        return rows;
    }


    private String addLimit(BaseEntity entity, String sql) {
        int fromRow = (entity.getPage() - 1) * entity.getRows();
        sql += " limit " + fromRow + "," + entity.getRows();
        return sql;
    }

    private String addWhere(BaseEntity entity, String sql) {
        if(!StringUtil.isNullOrEmpty(entity.getName())) {
            sql += " and types = '" + entity.getName() + "'";
        }
        return sql;
    }


    private String addOrderBy(BaseEntity entity, String sql) {
        if (entity.getSort() != null) {
            if("name".equals(entity.getSort())) entity.setSort("types");
            sql += " order by " + entity.getSort() + " " + entity.getOrder();
        }
        return sql;
    }
}
